package com.dy.yunying.biz.dao.doris.impl;

import com.dy.yunying.api.datacenter.dto.HourDataDto;
import com.dy.yunying.api.datacenter.vo.HourDataVo;
import com.dy.yunying.api.enums.DataReportEnum;
import com.dy.yunying.api.enums.HourDataKpiEnum;
import com.dy.yunying.biz.config.YunYingDorisTableProperties;
import com.dy.yunying.biz.utils.DateUtils;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.google.common.collect.Lists;
import com.sjda.framework.common.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName HourDataDao2
 * @Description todo
 * @Author nieml
 * @Time 2023/1/4 9:45
 * @Version 1.0
 **/
@Component(value = "hourDataDorisDao")
@Slf4j
public class HourDataDorisDao {

	@Resource(name = "dataLayerDorisTemplate")
	private JdbcTemplate dorisTemplate;
	@Resource
	private YunYingDorisTableProperties yunYingDorisTableProperties;
	@Resource
	private DataLayerOfflineRepairDao dataLayerOfflineRepairDao;
	final private String indentStr = "		";
	final private String indentStr1 = "		";
	final private String indentStr2 = "				";
	final private String indentStr3 = "						";

	/**
	 * 维度查询
	 * */
	public List<HourDataVo> getHourDataDim(HourDataDto req) {
		StringBuilder sql = getKpiSql(req);
		log.info("分时数据查询sql:[{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<HourDataVo> list = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "分时数据报表，耗时：" + (end - start) + "毫秒");
		//补数据
		fillDimData(req,list);
		return list;
	}

	/**
	 * 补维度查询的数据（queryColumn有值）
	 * */
	public void fillDimData(HourDataDto req,List<HourDataVo> dimList){
		Integer cycleType = req.getCycleType();
		String queryColumn = req.getQueryColumn();

		if (CollectionUtils.isEmpty(dimList)){
			//无需补
			return;
		}

		//汇总结果：pgid
		if (cycleType == 4){
			//无需补
			return;
		}

		//明细结果：hour+pgid
		if (cycleType == 0){
			try {
				//获取pgid值
				HashSet<Object> dimValuesSet = new HashSet<>();
				dimList.forEach(hourDataVo -> {
					Object dimFieldValue = getFieldValueByObject(hourDataVo,queryColumn);
					dimValuesSet.add(dimFieldValue);
				});
				//按维度组合补值
				List<Map<String,Object>> dimValueList = Lists.newArrayList();
				List<Integer> hourList = getHourList(req);
				for (int i = 0; i < hourList.size(); i++) {
					for (Object dimValue : dimValuesSet) {
						HashMap<String, Object> dimValueMap = new HashMap<>();
						dimValueMap.put("hour",i);
						dimValueMap.put(queryColumn,dimValue);
						dimValueList.add(dimValueMap);
					}
				}

				List<HourDataVo> newDimList = new ArrayList<>();
				dimValueList.forEach(dimValueMap->{
					Object hourValue = dimValueMap.get("hour");
					Object hourDataKpiValue = dimValueMap.get(queryColumn);
					Long hourValueLong = Long.valueOf(String.valueOf(hourValue));
					List<HourDataVo> existHourDataVo = dimList.stream().filter(dimRow -> dimRow.getHour().equals(hourValueLong)
								&& getFieldValueByObject(dimRow,queryColumn).equals(hourDataKpiValue)).collect(Collectors.toList());

					if (CollectionUtils.isEmpty(existHourDataVo)){
						HourDataVo hourDataVo = new HourDataVo();
						try {
							setfields(hourDataVo,"hour",String.valueOf(hourValue),queryColumn,String.valueOf(hourDataKpiValue));
						} catch (IllegalAccessException e) {
							log.error("fillDimData:{}",e);
							return;
						}
						newDimList.add(hourDataVo);
					}
				});
				dimList.addAll(newDimList);
			}catch (Exception e){
				log.error("fillDimData:{}",e);
				return;
			}
		}
	}

	/**
	 * 反射：给字段取值
	 * */
	public static Object getFieldValueByObject(Object object, String targetFieldName){
		// 获取该对象的Class
		Class objClass = object.getClass();
		// 初始化返回值
		Object result = null;
		// 获取所有的属性数组
		Field[] fields = objClass.getDeclaredFields();
		for (Field field : fields) {
			// 属性名称
			String currentFieldName = "";
			// 获取属性上面的注解 import com.fasterxml.jackson.annotation.JsonProperty;
			/**
			 * 举例： @JsonProperty("roleIds")
			 * private String roleIds;
			 */
			try {
				boolean has_JsonProperty = field.isAnnotationPresent(JsonProperty.class);

				if (has_JsonProperty) {
					currentFieldName = field.getAnnotation(JsonProperty.class).value();
				} else {
					currentFieldName = field.getName();
				}

				if (currentFieldName.equals(targetFieldName)) {
					field.setAccessible(true);
					result = field.get(object);
					return result; // 通过反射拿到该属性在此对象中的值(也可能是个对象)
				}
			} catch (SecurityException e) {
				// 安全性异常
				log.error("fillDimData:{}",e);
			} catch (IllegalArgumentException e) {
				// 非法参数
				log.error("fillDimData:{}",e);
			} catch (IllegalAccessException e) {
				// 无访问权限
				log.error("fillDimData:{}",e);
			}
		}
		return result;
	}


	/**
	 * 反射：给字段设置值
	 * */
	public static <T> boolean setfields(T entity,String hourFieldName,String hourFieldNameValue,String dimFieldName,String dimFieldNameValue) throws IllegalAccessException {

		Class<?> clazz = entity.getClass();
		try {
			// 获取字段
			Field hourField = clazz.getDeclaredField(hourFieldName);
			Field dimField = clazz.getDeclaredField(dimFieldName);
			// 开通权限
			hourField.setAccessible(true);
			dimField.setAccessible(true);
			// 赋值
			hourField.set(entity, Long.valueOf(hourFieldNameValue));
			if (dimFieldName.equals("gameid")|| dimFieldName.equals("pgid")){
				dimField.set(entity, Long.valueOf(dimFieldNameValue));
			}else {
				dimField.set(entity, dimFieldNameValue);
			}

			return true;
		} catch (NoSuchFieldException e) { // 此异常为 实体内字段不存在
			log.error("fillDimData:{}",e);
		}
		return false;
	}


	/**
	 * 汇总查询
	 * */
	public List<HourDataVo> getHourDataAll(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		req.setQueryColumn(null);
		StringBuilder allSql = getKpiSql(req);
		log.info("分时数据汇总查询sql:[{}]", allSql.toString());
		long start = System.currentTimeMillis();
		List<HourDataVo> allList = dorisTemplate.query(allSql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "分时数据报表，耗时：" + (end - start) + "毫秒");
		//补数据
		fillAllData(req,allList);
		req.setQueryColumn(queryColumn);
		return allList;
	}


	/**
	 * 补汇总查询的数据（queryColumn为空）
	 * */
	public void fillAllData(HourDataDto req,List<HourDataVo> allList){
		Integer cycleType = req.getCycleType();
		//汇总结果
		if (cycleType == 4){
			if (CollectionUtils.isEmpty(allList)){
				HourDataVo hourDataVo = new HourDataVo();
				allList.add(hourDataVo);
			}
		}
		//明细结果
		if (cycleType == 0){
			List<Integer> hourList = this.getHourList(req);
			if (CollectionUtils.isEmpty(allList)){
				for (Integer hour : hourList) {
					HourDataVo hourDataVo = new HourDataVo();
					hourDataVo.setHour(Long.valueOf(hour));
					allList.add(hourDataVo);
				}
			}else {
				for (Integer hour : hourList) {
					List<HourDataVo> filterList = allList.stream().filter(row -> row.getHour().equals(Long.valueOf(hour))).collect(Collectors.toList());
					//不存在当前hour，补一条数据
					if (CollectionUtils.isEmpty(filterList)){
						HourDataVo hourDataVo = new HourDataVo();
						hourDataVo.setHour(Long.valueOf(hour));
						allList.add(hourDataVo);
					}
				}
			}
		}

	}


	// 获取小时List
	private List<Integer> getHourList(HourDataDto req) {
		String newteday = DateUtils.dateToString(new Date(), DateUtils.YYYYMMDD);// 今天
		Long startTime = req.getRsTime();
		String dateStr = Long.toString(startTime);
		if (dateStr.equals(newteday)) {
			List<Integer> list = new ArrayList<Integer>();
			Integer hour = new Date().getHours();
			for (int i = 0; i <= hour; i++) {
				list.add(hour - i);
			}
			return list;
		}
		List<Integer> hourList = Arrays.asList(23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0);
		return hourList;
	}


	/**
	 * 指标sql：新增设备、新增充值金额、LTV；付费设备
	 * */
	private StringBuilder getKpiSql(HourDataDto req){
		StringBuilder periodSql = this.getPeriodSql(req);
		StringBuilder selectDimFieldsSql = this.getSelectDimFieldsSql(req);
		StringBuilder groupByFieldsSql = this.getGroupByFieldsSql(req);
		StringBuilder whereFieldsSql = this.getWhereFieldsSql(req);
		StringBuilder kpiFieldSql = getKpiFieldSql(req);
		StringBuilder allFieldsSql = this.getAllFieldsSql(req);
		String isGroupBy = StringUtils.isBlank(periodSql.toString())&&StringUtils.isBlank(groupByFieldsSql.toString()) ? String.valueOf(false) : String.valueOf(true);

		StringBuilder sql = new StringBuilder();
		sql.append(" select ").append("\n");
		sql.append(periodSql);
		sql.append(StringUtils.isBlank(periodSql.toString()) ? "" : "," );
		sql.append(selectDimFieldsSql).append("\n");
		sql.append(kpiFieldSql);
		sql.append(" from ").append("\n");
		sql.append(allFieldsSql).append("\n");
		sql.append(" where 1=1 ").append("\n");
		sql.append(whereFieldsSql).append("\n");
		if (Boolean.valueOf(isGroupBy)){
			sql.append(" group by ").append("\n");
			sql.append(periodSql);
			sql.append(StringUtils.isBlank(periodSql.toString()) || StringUtils.isBlank(groupByFieldsSql.toString()) ? "" : "," );
			sql.append(groupByFieldsSql).append("\n");
		}
		return sql;
	}

	/**
	 * 时间维度字段
	 * */
	private StringBuilder getKpiFieldSql(HourDataDto req) {
		StringBuilder sql = new StringBuilder();
		String hourDataKpi = req.getHourDataKpi();
		if (HourDataKpiEnum.PAYDEVICES.getName().equals(hourDataKpi)){
			sql.append(" COALESCE(bitmap_union_count(active_device_pay_bitmap), 0) payAmount -- 累计充值人数即付费设备 ").append("\n");
		}else if (HourDataKpiEnum.REG.getName().equals(hourDataKpi)){
			sql.append(" COALESCE(bitmap_union_count(new_device_bitmap), 0) newRegNums -- 新增设备数 ").append("\n");
		}else if (HourDataKpiEnum.PAY.getName().equals(hourDataKpi)){
			sql.append(" COALESCE(sum(new_fee_1),0) newRegPayAmount -- 新增充值金额 ").append("\n");
		}else {
			sql.append(" COALESCE(bitmap_union_count(new_device_bitmap), 0) newRegNums, -- 新增设备数 ").append("\n");
			sql.append(" COALESCE(sum(new_fee_1),0) newRegPayAmount -- 新增充值金额 ").append("\n");
		}
		return sql;
	}


	/**
	 * 时间维度字段
	 * */
	private StringBuilder getPeriodSql(HourDataDto req) {
		Integer cycleType = req.getCycleType();
		String period = "";
		if (cycleType == 0){
			period = " hour ";
		}
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}


	/**
	 * 子查询：获取所有必需字段
	 * */
	private StringBuilder getAllFieldsSql(HourDataDto req){
		Long rsTime = req.getRsTime();
		Long reTime = req.getReTime();
		// 过滤日期字符串
		String filterDayStr = HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi()) ? yunYingDorisTableProperties.getACTIVE_DAY() : yunYingDorisTableProperties.getREG_DAY();

		// 查询日期字符串
		StringBuilder dateSelectSql = getDateSelectSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(" ( ").append("\n");
		sql.append(" select ").append("\n");
		//日期字段
		sql.append(dateSelectSql).append("\n");
		sql.append(" '汇总' as collect,ar.media_code,ar.chl_app as appchl,ar.ad_id as adid, ").append("\n");
		sql.append(" dimchl.game_main as pgid,dimchl.game_sub as gameid,dimchl.chl_main as parentchl,dimchl.investor as investor,dimchl.dept_id as deptId,dimchl.dept_group_id as userGroupId,dimchl.os as os,dimchl.game_main_name as parentGameName,dimchl.game_sub_name as gameName,dimchl.dept_name as deptName,dimchl.dept_group_name as userGroupName, dimchl.investor_name as investorName,dimchl.chl_main_name as parentchlName,dimchl.spread_type spread_type, ").append("\n");
		sql.append(" new_device_bitmap, -- 新增设备 ").append("\n");
		sql.append(" new_fee_1, -- 新增充值实付金额 ").append("\n");
		sql.append(" active_device_pay_bitmap -- 活跃付费设备数  ").append("\n");
		sql.append(" from ").append("\n");

		sql.append(dataLayerOfflineRepairDao.getAdsOperation3399AdReportSql(rsTime,reTime,filterDayStr,DataReportEnum.HOURDATA.getType()));
		sql.append(" left join ").append("\n");
		sql.append(yunYingDorisTableProperties.getVDimOperation3399ChannelGameInvestor()).append(" dimchl ").append("\n");
		sql.append(" on ar.chl_app = dimchl.chl_app ").append("\n");
		sql.append(" ) ar ").append("\n");
		return sql;
	}


	/**
	 * where 日期
	 * */
	private StringBuilder getDateSelectSql(HourDataDto req){
		String hourDataKpi = req.getHourDataKpi();
		StringBuilder sql = new StringBuilder();
		if(HourDataKpiEnum.PAYDEVICES.getType().equals(hourDataKpi)){
			sql.append(" active_day as day,hour as hour, ");
		}else {
			sql.append(" reg_day as day,reg_hour as hour, ");
		}
		return sql;
	}


	/**
	 * where Fields：注册时间、活跃时间、主渠道、主游戏、子游戏、部门、投放人
	 * */
	private StringBuilder getWhereFieldsSql(HourDataDto req) {
		final String parentchlArr = req.getParentchlArr();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String deptIdArr = req.getDeptIdArr();
		final String investorArr = req.getInvestorArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final Integer os = req.getOs();
		final String appchlArr = req.getAppchlArr();

		StringBuilder sql = new StringBuilder();
		sql.append(" and spread_type <> 2 ");
		if (StringUtils.isNotBlank(pgidArr)) {
			sql.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			sql.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			sql.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			sql.append(" AND appchl IN ('").append(appchlArr.replaceAll(",", "', '")).append("')");
		}
		if (StringUtils.isNotBlank(deptIdArr)) {
			sql.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			sql.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			sql.append(" AND investor IN (").append(investorArr).append(")");
		}
		if (os != null) {
			sql.append(" AND os = ").append(os);
		}
		// 账号渠道权限
		if (req.getIsSys() != 1) {
			sql.append(" AND investor IN (").append(req.getUserIds()).append(")");
		}
		return sql;
	}


	/**
	 * groupby Fields
	 * 渠道、转化目标、广告账户、广告计划、主游戏、子游戏、分包、系统、部门、投放人、组别
	 * */
	private StringBuilder getGroupByFieldsSql(HourDataDto req) {
		StringBuilder groupColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			groupColumnSql.append(queryColumn);
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(", deptName");
			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(", userGroupName");
			}
			if (queryColumn.contains("pgid")) {
				groupColumnSql.append(", parentGameName");
			}
			if (queryColumn.contains("gameid")) {
				groupColumnSql.append(", gameName");
			}
			if (queryColumn.contains("parentchl")) {
				groupColumnSql.append(", parentchlName");
			}
		}
		return groupColumnSql;
	}


	/**
	 * select dim Fields
	 * */
	private StringBuilder getSelectDimFieldsSql(HourDataDto req) {
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		if (StringUtils.isNotBlank(queryColumn)) {
			queryColumnSql.append(queryColumn).append(",");
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			if (queryColumn.contains("pgid")) {
				queryColumnSql.append("parentGameName,");
			}
			if (queryColumn.contains("gameid")) {
				queryColumnSql.append("gameName,");
			}
			if (queryColumn.contains("parentchl")) {
				queryColumnSql.append("parentchlName,");
			}
			queryColumnSql.append("\n");
		}
		return queryColumnSql;
	}


}
